using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class MyConnection
    {

        private SqlServerConnection connection;

        public MyConnection()
        {

        }

        /// 
        /// <param name="connectionString"></param>
        public MyConnection(string connectionString)
        {
            this.connection = new SqlServerConnection(connectionString);
        }

        /// 
        /// <param name="condition"></param>
        /// <param name="query"></param>
        public void AddCondition(StringBuilder query,string condition)
        {
            if ((condition != null) && (!"".Equals(condition)))
            {
                query.Append(" WHERE ").Append(condition);
            }
        }

        /// 
        /// <param name="query"></param>
        /// <param name="orderBy"></param>
        public void AddOrderBy(StringBuilder query, string orderBy)
        {
            if ((orderBy != null) && (!"".Equals(orderBy)))
            {
                query.Append(" ORDER BY ").Append(orderBy);
            }
        }

        /// 
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <param name="orderBy"></param>
        public SqlDataReader Select(string tableName, string condition, string orderBy)
        {
            StringBuilder query = new StringBuilder("SELECT * FROM " + tableName);
            this.AddCondition(query, condition);
            this.AddOrderBy(query, orderBy);
            query.Append(";");
            return this.connection.ExecuteQuery(query.ToString());
        }

        /// 
        /// <param name="top"></param>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <param name="orderBy"></param>
        public SqlDataReader SelectTop(int top, string tableName, string condition, string orderBy)
        {
            StringBuilder query = new StringBuilder("SELECT TOP " + top + " * FROM " + tableName);
            this.AddCondition(query, condition);
            this.AddOrderBy(query, orderBy);
            query.Append(";");
            return this.connection.ExecuteQuery(query.ToString());
        }

        /// 
        /// <param name="tableName"></param>
        /// <param name="columnValues"></param>
        public void Insert(string tableName, List<DataStruct> columnValues)
        {
            SqlCommand cmd = this.connection.GetCommand();
            cmd.Parameters.Clear();
            StringBuilder query = new StringBuilder("INSERT INTO " + tableName);
            StringBuilder values = new StringBuilder();
            query.Append(" (");
            foreach (DataStruct entry in columnValues)
            {
                query.Append(entry.columnName).Append(", ");
                values.Append("@" + entry.columnName).Append(", ");
                cmd.Parameters.Add("@" + entry.columnName, entry.dataType).Value = entry.value ?? DBNull.Value;
    
            }

            query.Remove(query.Length - 2, 2);
            values.Remove(values.Length - 2, 2);
            query.Append(") VALUES (").Append(values.ToString()).Append(");");
            cmd.CommandText = query.ToString();
            cmd.ExecuteNonQuery();
        }

        /// 
        /// <param name="tableName"></param>
        /// <param name="columnValues"></param>
        /// <param name="condition"></param>
        public void Update(string tableName, List<DataStruct> columnValues, string condition)
        {
            SqlCommand cmd = this.GetCommand();
            cmd.Parameters.Clear();
            StringBuilder query = new StringBuilder("UPDATE " + tableName + " SET ");
            StringBuilder values = new StringBuilder();
            foreach (DataStruct entry in columnValues)
            {
                query.Append(entry.columnName).Append(" = ").Append("@" + entry.columnName).Append(", ");
                cmd.Parameters.Add("@" + entry.columnName, entry.dataType).Value = entry.value ?? DBNull.Value;
            }
            query.Remove(query.Length - 2, 2);
            this.AddCondition(query, condition);
            cmd.CommandText = query.ToString();
            cmd.ExecuteNonQuery();
        }

        /// 
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        public void Delete(string tableName, string condition)
        {
            StringBuilder query = new StringBuilder("DELETE FROM " + tableName);
            this.AddCondition(query, condition);
            query.Append(";");

            this.connection.ExecuteNonQuery(query.ToString());
        }

        public void Disconnect()
        {
            this.connection.Disconnect();
        }

        public SqlServerConnection GetConnection()
        {
            return this.connection;
        }

        public SqlCommand GetCommand()
        {
            return this.connection.GetCommand();
        }
    }//end MyConnection
}